<?php
class Db_Products_Categories extends Common{
	protected static $tableName = TAB_CATEGORIES;
	protected static $dbFields = array(
		'id',
		'pc_name',
		'pc_name_plural',
		'pc_teaser_title',
		'pc_teaser_content',
		'pc_teaser_link_text',
		'pc_default_product_id',
		'pc_show_envelope',
		'deleted'
	);

	public $id;
	public $pc_name;
	public $pc_name_plural;
	public $pc_teaser_title;
	public $pc_teaser_content;
	public $pc_teaser_link_text;
	public $pc_default_product_id;
	public $pc_show_envelope;
	public $deleted;

	public $arrayObject;

	function __construct($value=NULL, $fieldName=NULL){
		parent::__construct($value, $fieldName);
	}

	/**
	 * This function return all categories which are used and not used in products
	 *
	 * @static
	 * @return mixed
	 */
	public static function getAllCategories() {
		$sql = "SELECT `pc`.*, `pc`.`id` AS `pc_id`, IF(`prc`.`prc_category_id` = `pc`.`id`, 1, 0) AS `is_used`
				FROM ?f AS `pc`
				LEFT JOIN ?f AS `prc` ON `prc`.`prc_category_id` = `pc`.`id`
				WHERE `pc`.`deleted` = 0 GROUP BY `pc`.`id`";
		return self::$DB->select($sql, self::$tableName, TAB_PRODUCT_CATEGORIES);
	}

	/**
	 * This Function return distinct Projects Categories which are used By page Id
	 *
	 * @static
	 * @param int $pageId
	 * @return mixed
	 */
	public static function getCategoriesByPageId($pageId) {
		$sql = 'SELECT `pc`.`pc_name`, `pc`.`id` FROM ?f AS `p`
				INNER JOIN ?f AS `mp` ON `p`.`id` = `mp`.`mp_page_id` AND `mp`.`deleted` = 0
				INNER JOIN ?f AS `m` ON `m`.`id` = `mp`.`mp_menu_id` AND `m`.`deleted` = 0
				INNER JOIN ?f AS `pr` ON `pr`.`pr_identifier` = `m`.`m_project_identifier` AND `pr`.`deleted` = 0
				INNER JOIN ?f AS `cp` ON `cp`.`cp_project_id` = `pr`.`id`
				INNER JOIN ?f AS `pc` ON `pc`.`id` = `cp`.`cp_category_id` AND `pc`.`deleted` = 0
				WHERE `p`.`id` = ? AND `p`.`deleted` = 0 GROUP BY `pc`.`id` ;';
		return self::$DB->select($sql, TAB_PAGES, TAB_MENU_PAGES, TAB_MENUS, TAB_PROJECTS, TAB_CATEGORY_PROJECTS, self::$tableName, $pageId );
	}

	public static function getProjectIdsByCategoryId($catId){
		$sql = "SELECT `cp`.`cp_project_id` FROM ?f cp WHERE `cp`.`cp_category_id` = ?d";
		return self::$DB->selectCol($sql, TAB_CATEGORY_PROJECTS, $catId);
	}

	public static function rewriteCategoryProjects($catId, $projectIds){
		$sql = "SELECT `cp`.`cp_project_id` FROM ?f cp
					INNER JOIN ?f c ON `c`.`id` = `cp`.`cp_category_id` AND `c`.`deleted` = 0
					INNER JOIN ?f prc ON `prc`.`prc_category_id` = `c`.`id`
					INNER JOIN ?f p ON `p`.`id` = `prc`.`prc_product_id` AND `p`.`deleted` = 0
					INNER JOIN ?f prc_new ON `prc_new`.`prc_category_id` = ?d
					INNER JOIN ?f p_new ON `p_new`.`id` = `prc_new`.`prc_product_id` AND `p_new`.`deleted` = 0
				WHERE `cp`.`cp_project_id` IN (?) AND NOT `cp`.`cp_category_id` = ?d AND `p`.`id` = `p_new`.`id`
				GROUP BY `cp`.`cp_project_id` HAVING COUNT(`p`.`id`) > 0";
		$resultProducts = self::find($sql, TAB_CATEGORY_PROJECTS, TAB_CATEGORIES, TAB_PRODUCT_CATEGORIES, TAB_PRODUCTS,
			TAB_PRODUCT_CATEGORIES, $catId, TAB_PRODUCTS, $projectIds, $catId);
		if ($resultProducts && count($resultProducts)) {
			return false;
		}
		$sql = "DELETE FROM ?f WHERE `cp_category_id`  = ?d AND NOT `cp_project_id` IN (?)";
		$result = self::$DB->q($sql, TAB_CATEGORY_PROJECTS, $catId, $projectIds);
		$data = array();
		foreach ($projectIds as $projectId) {
			$data[] = array(
				'cp_category_id' => $catId,
				'cp_project_id' => $projectId
			);
		}
		return $result && self::$DB->insertBatch(TAB_CATEGORY_PROJECTS, $data, true);
	}

	/**
	 * This function gets all Categories, fields (Id, Name) and all products which belong each category
	 * @static
	 * @return array
	 */
	public static function getCategories() {
		$productsListId = Db_Products_Products::findAllArray("id");
		$sql = "SELECT `c`.`id` as `category_id`, `c`.`pc_name`, `prc`.`prc_product_id` FROM ?f c
		LEFT JOIN ?f `prc` ON `prc`.`prc_category_id` = `c`.`id`
		WHERE `c`.`deleted` = 0
		GROUP BY `c`.`id`, `prc_product_id`";

		$categoryProducts = self::$DB->q(true, $sql, self::$tableName, TAB_PRODUCT_CATEGORIES);

		$productsId = array();
		$dataReturn = array();
		$productIdArray = array();
		if ($productsListId) {
			foreach ($productsListId as $productId) {
				$productIdArray[] = $productId['id'];
			}
		}
		while ($data = $categoryProducts->fetch_assoc()) {
			if (isset($data['category_id']) && in_array($data['prc_product_id'], $productIdArray)){
				$productsId[$data['category_id']][] = "prod".$data['prc_product_id'];
			}
			if (!isset($productsId[$data['category_id']])) {
				$productsId[$data['category_id']] = array();
			}

			$countCategoryProducts = count($productsId[$data['category_id']]);
			$dataReturn[$data['category_id']] = array(
				"id" => "cat".$data['category_id'],
				"name" => t(T_CATEGORY_TITLE, $data['pc_name'])." (".$countCategoryProducts.")",
				"products" => $productsId[$data['category_id']]
			);
		}
		return array_values($dataReturn);
	}

	/**
	 * This function return category by Id
	 * @static
	 * @param $categoryId
	 * @return mixed
	 */
	public static function getCategoryById($categoryId) {
		$projectIdentifier = self::$PROJECT['pr_identifier'];
		return self::$DB->selectRow('SELECT `ct`.*, `p`.`p_url` FROM ?f AS `ct`
									INNER JOIN ?f AS `p` ON `p`.`p_pagination_category_id` = `ct`.`id`
									INNER JOIN ?f AS `mp` ON `mp`.`mp_page_id` = `p`.`id`
									INNER JOIN ?f AS `m` ON `m`.`id` = `mp_menu_id` AND `m`.`m_project_identifier` = ?
									WHERE `ct`.`deleted` = 0 AND `p`.`deleted` = 0 AND `mp`.`deleted` = 0 AND `m`.`deleted` = 0 AND `ct`.`id` = ?
									GROUP BY `ct`.`id`;',
									self::$tableName, TAB_PAGES, TAB_MENU_PAGES, TAB_MENUS, $projectIdentifier, $categoryId);
	}

	public static function getCategoriesWithDefaultProductsByProjectId($projectId){
		return self::find("SELECT `c`.`pc_name`, `ph`.`ph_path`, `ph`.`ph_resized`, `pg`.`p_url`, `ph`.`ph_rw`,
							`ph`.`ph_rh`
							FROM ?f pr
								INNER JOIN ?f cp ON `cp`.`cp_project_id` = `pr`.`id`
								INNER JOIN ?f c ON `cp`.`cp_category_id` = `c`.`id` AND `c`.`deleted` = 0
								INNER JOIN ?f p ON `c`.`pc_default_product_id` = `p`.`id` AND `p`.`deleted` = 0
								INNER JOIN ?f m ON `m`.`m_project_identifier` = `pr`.`pr_identifier`
								INNER JOIN ?f mp ON `m`.`id` = `mp`.`mp_menu_id`
								INNER JOIN ?f pg ON `mp`.`mp_page_id` = `pg`.`id` AND `pg`.`p_dynamic_url` = 1
									AND `pg`.`p_pagination_type` = ? AND `pg`.`deleted` = 0
									AND `pg`.`p_pagination_category_id` = `c`.`id`
								INNER JOIN ?f ptf ON `ptf`.`pt_product_id` = `p`.`id`
								INNER JOIN ?f ph ON `ptf`.`pt_photo_id` = `ph`.`id` AND `ph`.`deleted` = 0
							WHERE `pr`.`id` = ?d AND `pr`.`deleted` = 0
							GROUP BY `p`.`id`"
		, TAB_PROJECTS, TAB_CATEGORY_PROJECTS, TAB_CATEGORIES, TAB_PRODUCTS, TAB_MENUS, TAB_MENU_PAGES, TAB_PAGES,
			'category', TAB_PRODUCT_THEME_PHOTOS, TAB_PHOTOS, $projectId);
	}
	public static function getCategoriesByProjectId($projectId) {
		$sql = 'SELECT `c`.`id` AS `category_id`, `c`.`pc_name` FROM ?f cp
		        INNER JOIN ?f `c` ON `c`.`id` = `cp`.`cp_category_id`
		        WHERE `cp`.`cp_project_id` = ? AND `c`.`deleted` = 0';
		return self::$DB->select($sql, TAB_CATEGORY_PROJECTS, self::$tableName, $projectId);
	}

	/**
	 * @static
	 * @param $categories
	 * @return mixed
	 */
	public static function verifyIsValidCategories($categories) {
		$sql = "SELECT COUNT(`cp`.`cp_project_id`) AS `pr_count`
				FROM ?f AS `ct`
				INNER JOIN ?f AS `cp` ON `cp`.`cp_category_id` = `ct`.`id`
				INNER JOIN ?f AS `pr` ON `pr`.`id` = `cp`.`cp_project_id`
				WHERE `ct`.`deleted` = 0 AND `pr`.`deleted` = 0 AND `ct`.`id` IN ( ? )
				GROUP BY `cp`.`cp_project_id`
				ORDER BY `pr_count` DESC LIMIT 1";
		return self::$DB->selectCell($sql, self::$tableName, TAB_CATEGORY_PROJECTS, TAB_PROJECTS, $categories);
	}

	/**
	 * @static
	 * @param $parentCategoryId
	 * @return array
	 */
	public static function getUnusedCategoriesForOffers ($parentCategoryId) {
		$usedCategories = Db_Products_CategoryOffers::findAllByField('co_parent_category_id', $parentCategoryId);
		$usedCategoriesId = array();
		if($usedCategories) {
			foreach ($usedCategories as $usedCategory) {
				$usedCategoriesId[] = $usedCategory->co_connected_category_id;
			}
		}
		$usedCategoriesId[] = $parentCategoryId;

		$sql = "SELECT * FROM ?f `ct`
		        INNER JOIN ?f AS `cp` ON `cp`.`cp_category_id` = `ct`.`id`
		        INNER JOIN ?f AS `pr` ON `pr`.`id` = `cp`.`cp_project_id`
		        WHERE `ct`.`deleted` = 0 AND `pr`.`deleted` = 0 AND `ct`.`id` NOT IN  ( ? )
		        GROUP BY `cp`.`cp_project_id`";
		return self::$DB->select($sql, self::$tableName, TAB_CATEGORY_PROJECTS, TAB_PROJECTS, $usedCategoriesId);
	}
}